#
#用于修正已录入系统ICBill没有价格的数据
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#手工在命令行运行 ruby fix_icbill.rb
#
require 'mysql2'

    rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])

    priceHashP = {}; #Price hash in PSI goods_id
    priceHashM = {}; #Price hash in Magento code

    #prepare price reference table
    sql1 = 'select * from psi.t_goods'
    res1 = rds.query(sql1)
    res1.each do |tgr|
        code = tgr['code']
        id = tgr['id']
        name = tgr['name']
        purchase_price = tgr['purchase_price'];
        priceHashP.store(id,purchase_price);
        priceHashM.store(code,purchase_price);
    end

    #update ic_bill_details
    sql2 = "select * from psi.t_ic_bill_detail where icbill_id in (select id from psi.t_ic_bill where bill_status=0 and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963')"
    res2 = rds.query(sql2)

    res2.each do |tgr|
        id = tgr['id']
        goods_id = tgr['goods_id']
        goods_count = tgr['goods_count']
        goods_money = sprintf("%.2f", goods_count * priceHashP[goods_id]);
        sqlu = "update psi.t_ic_bill_detail set goods_money = #{goods_money} where id = '#{id}'"
        puts 'executing: ' + sqlu;
        # uncomment to record
        # resu = rds.query(sqlu);
    end

=begin

    #update t_inventory_detail
    sql3="select * from psi.t_inventory_detail where goods_id in (select goods_id from psi.t_ic_bill_detail where icbill_id in (select id from psi.t_ic_bill where bill_status=0 and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963')) and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963'"
    res3 = rds.query(sql3)
    res3.each do |tgr|
        id = tgr['id']
        goods_id = tgr['goods_id']
        goods_count = tgr['in_count']
        goods_price = sprintf("%.2f", priceHashP[goods_id]);
        goods_money = sprintf("%.2f", goods_count * priceHashP[goods_id]);
        sqlu = "update psi.t_inventory_detail set balance_money=#{goods_money}, balance_price=#{goods_price}, in_money=#{goods_money}, in_price=#{goods_price} where id = '#{id}'"
        puts 'executing: ' + sqlu;
        #uncomment to record
        resu = rds.query(sqlu);
    end

    sql4="select * from psi.t_inventory where goods_id in (select goods_id from psi.t_ic_bill_detail where icbill_id in (select id from psi.t_ic_bill where warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and bill_status=1000 )) and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963'"
    res4 = rds.query(sql4)
    res4.each do |tgr|
        id = tgr['id']
        goods_id = tgr['goods_id']
        goods_count = tgr['in_count']
        goods_price = sprintf("%.2f", priceHashP[goods_id]);
        goods_money = sprintf("%.2f", goods_count * priceHashP[goods_id]);
        sqlu = "update psi.t_inventory set balance_money=#{goods_money}, balance_price=#{goods_price}, in_money=#{goods_money}, in_price=#{goods_price} where id = '#{id}'"
        puts 'executing: ' + sqlu;
        #uncomment to record
        resu = rds.query(sqlu);
    end
=end

    # verify price is good
    puts sprintf("%.2f",priceHashP['7246CA6B-E611-11E8-8B92-6C92BF475963']);
    puts sprintf("%.2f",priceHashM['41000090']);

rds.close
